<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
                
// Set flag that this is a parent file.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();
         
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:K1');
$activeSheet->setCellValue('A1', 'Outlets Revenue Reports');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);
//echo intval(101000/50000); die;

/************** FILL HEADER **************/
$index = 5;
$activeSheet->setCellValue('A'.$index, 'Outlet Name');
$activeSheet->setCellValue('B'.$index, 'Cover');
$activeSheet->setCellValue('C'.$index, 'Food');
$activeSheet->setCellValue('D'.$index, 'Beverage Alcoholic');
$activeSheet->setCellValue('E'.$index, 'Misc./Ciger');
$activeSheet->setCellValue('F'.$index, 'VAT + Service Charge');

$activeSheet->setCellValue('G'.$index, 'Total');
$activeSheet->setCellValue('H'.$index, 'Average Spending per cover');
$activeSheet->setCellValue('I'.$index, 'Food ratio');
$activeSheet->setCellValue('J'.$index, 'BV Ratio');
$activeSheet->setCellValue('K'.$index, 'Misc Ratio');

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K');
foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, 5, 35);
    }
    else if($item == 'D')
    {
        setBackgroundColor($activeSheet, $item, 5, 28);
    }
    else if($item == 'E' || $item == 'F')
    {
        setBackgroundColor($activeSheet, $item, 5, 25);
    }
    else if($item == 'H')
    {
        setBackgroundColor($activeSheet, $item, 5, 30);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 5);   
    }
}
/************ PRE INIT ************/
$mainframe = &JFactory::getApplication();
$context = JRequest::getCmd('option');
$view = JRequest::getCmd('view');
$db = JFactory::getDbo();

/************** Outlet list **************/
$activeSheet->mergeCells('A3:K3');
$activeSheet->setCellValue('A3', 'Outlet: All');   
$activeSheet->getStyle('A3')->getFont()->setSize(16);
$activeSheet->getStyle('A3')->getFont()->setBold(true);


/************* Query Data *************/
$query = "
    SELECT
        `o`.NAME AS `outlet`,
        SUM(`t`.pax) AS `cover`,
        SUM(`t`.food) AS `food`,
        SUM(`t`.beverage) AS `beverage`,
        SUM(`t`.other) AS `other`,
        SUM(`t`.vat) AS `vat`,
        SUM(`t`.sc) AS `sc`,
        SUM(`t`.total) AS `total`
    FROM #__wrd_outlets `o`,
        #__wrd_transaction `t`
    WHERE `t`.CONVERT = 1
          AND `o`.cid = '01'
          AND `t`.outlet = `o`.CODE
";


/************ CHECK CONDITIONS ************/

if(strval($_GET['f']) != ''){
    $query .= ' AND `t`.date_receipt >= "'.strval($_GET['f']).' 00:00:00'.'"'; 
}
else {
	$query .= ' AND `t`.date_receipt >= "'.date("Y-m")."-01".' 00:00:00'.'"'; 
}
if(strval($_GET['t']) != ''){
    $query .= ' AND `t`.date_receipt <= "'.strval($_GET['t']).' 23:59:59'.'" ' ; 
}
else {
	$query .= ' AND `t`.date_receipt <= "'.date("Y-m-d").' 23:59:59'.'" ' ;
}
$query .= '
    GROUP BY `o`.NAME
    ORDER BY total DESC
';
if(intval($_GET['l']) > 0)
{
    $query .= ' LIMIT 0, '.intval($_GET['l']);    
}

//echo $query; die;
            
$db->setQuery($query) ;
$data = $db->loadObjectList();

/******************* BIND DATA TO EXCEL *******************/
if(!empty($data))
{
    /*********** BIND TO EXCEL ***********/
    $index = 6;
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {
        $activeSheet->setCellValue('A'.$index, $data[$i]->outlet);            
            
        $activeSheet->setCellValue('B'.$index, $data[$i]->cover);
        $activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        
        $activeSheet->setCellValue('C'.$index, number_format($data[$i]->food, 0, ',', ','));     
        $activeSheet->getStyle('C'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
        $activeSheet->setCellValue('D'.$index, number_format($data[$i]->beverage, 0, ',', ','));
        $activeSheet->getStyle('D'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
        $activeSheet->setCellValue('E'.$index, number_format($data[$i]->other, 0, ',', ','));
        $activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
            
        $activeSheet->setCellValue('F'.$index, number_format((int)$data[$i]->vat + (int)$data[$i]->sc, 0, ',', ','));
        $activeSheet->getStyle('F'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
        
        $total = (int)$data[$i]->food + (int)$data[$i]->beverage + (int)$data[$i]->other;                
        $activeSheet->setCellValue('G'.$index, number_format(((int)$data[$i]->total), 0, ',', ','));
        $activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
        $activeSheet->setCellValue('H'.$index, number_format((float)$total/$data[$i]->cover, 0, '.', ','));
        $activeSheet->getStyle('H'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        
        $activeSheet->setCellValue('I'.$index, round ($data[$i]->food*100/$total).'%');
        $activeSheet->getStyle('I'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $activeSheet->setCellValue('J'.$index, round ($data[$i]->beverage*100/$total).'%');
        $activeSheet->getStyle('J'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $activeSheet->setCellValue('K'.$index, ( 100 - round ($data[$i]->beverage*100/$total) - round ($data[$i]->food*100/$total) ).'%');
        $activeSheet->getStyle('K'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
        $index++;
    }
    unset($data);
}


$fileName = 'outlet_report_revenue'.date('m_d_Y_H_i_s');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $fill = $activeSheet->getStyle($colName.$colIndex)->getFill();
    $fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    $fill->getStartColor()->setARGB('cc99ff');  
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
